*This program does a basic cleanup on the state level MCAS files
* June 2024 SRC edit to be specific about MCAS exclusions:
** exclude 2015/2016 grades 3-8 due to dual administration of PARCC and MCAS (districts select)
** exclude 2020 due to pandemic

clear
set more off
set mem 6g



local raw "$raw\MCAS"
local saves "$raw\saves"
cd "`raw'" 


***************SWITCHES***************
global import       "1"
global yearly 		"1"
global append 		"1"
global clean1 		"1"
global clean2 		"1"
global clean3		"1"

* convert dta from spss to dta;

if "${import}"=="1"{
import spss using "`raw'\2002 megafile 04.29.04 for data warehouse unique id.sav", clear
save "`raw'\mcas2002.dta", replace

import spss using "`raw'\2003 megafile 4.13.04 for data warehouse unique id.sav", clear
save "`raw'\mcas2003.dta", replace

import spss using "`raw'\2004 megafile 03.22.05 for data warehouse unique id.sav", clear
save "`raw'\mcas2004.dta", replace

import spss using "`raw'\2005 megafile 4.18.06 for data warehouse w race7 unique id.sav", clear
save "`raw'\mcas2005.dta", replace

import spss using "`raw'\2006 megafile 1.26.07 for data warehouse unique id.sav", clear
save "`raw'\mcas2006.dta", replace

import spss using "`raw'\mcas2007 megafile_final 11.15.07 for data warehouse unique id.sav", clear
save "`raw'\mcas2007.dta", replace

import spss using "`raw'\Megafile 2008 with Growth_no grd10 growth.sav", clear
save "`raw'\mcas2008.dta", replace

import spss using "`raw'\Megafile 2009 with Growth_official_09 16 2009.sav", clear
save "`raw'\mcas2009.dta", replace

import spss using "`raw'\MCAS 2010 official megafile_8.30.2010.sav", clear
save "`raw'\mcas2010.dta", replace

import spss using "`raw'\MCAS2011 official megafile_10.3.2011_Dryden suppressed.sav", clear
save "`raw'\mcas2011.dta", replace

import spss using "`raw'\MCAS 2012 official megafile_9.5.2012_Dryden suppressed.sav", clear
save "`raw'\mcas2012.dta", replace

import spss using "`raw'\MCAS2013_official megafile_EDW_9.6.2013_Dryden suppressed.sav", clear
save "`raw'\mcas2013.dta", replace

import spss using "`raw'\MCAS2014_official_8.29.2014_suppress Eliot & Mulcahey.sav", clear
save "`raw'\mcas2014.dta", replace

import spss using "`raw'\MCAS2015_official megafile 2015.9.11_new scitry.sav", clear
save "`raw'\mcas2015.dta", replace

import spss using "`raw'\MCAS2016_official megafile_new gr8 PARCC math items_10.7.16.sav", clear
save "M:\Massachusetts\data_raw\MCAS\mcas2016.dta", replace

import spss using "`raw'\MCAS2017_official_10.5.2017_Chandler suppressed_research master.sav", clear
save "`raw'\mcas2017.dta", replace

import spss using "`raw'\Spring 2018 MCAS_official_2018.10.5_research master.sav", clear
save "`raw'\mcas2018.dta", replace

import spss using "`raw'\MCAS2019_official megafile_9.5.19 edited & new accom flags.sav", clear
save "`raw'\mcas2019.dta", replace

import spss using "`raw'\MCAS 2020 Feb. Bio megafile_4.17.2020_more NTL.sav", clear
save "`raw'\mcas2020.dta", replace

import spss using "`raw'\MCAS2021_research master_with sasid (1).sav", clear
save "`raw'\mcas2021.dta", replace

import spss using "`raw'\MCAS2022.sav", clear
save "`raw'\mcas2022.dta", replace

import spss using "`raw'\MCAS2023_official megafile 9.7.23_new after suppression.sav", clear
save "`raw'\mcas2023.dta", replace

}

if "${yearly}"=="1"{

use "`raw'\mcas2002.dta"
gen year=2002
foreach v of varlist *item* {
	qui replace `v'="1" if `v'=="+"
	qui replace `v'="0" if `v'=="A"|`v'=="B"|`v'=="C"|`v'=="D"
	qui replace `v'="" if `v'=="*"
	qui destring `v', replace
	}
sort sasid year
save "`saves'\mcas2002items.dta", replace 	
drop *item* 
sort sasid year
cap tostring lepfirst, replace
save "`saves'\mcas2002y.dta", replace

clear
use "`raw'\mcas2003.dta"
gen year=2003
foreach v of varlist *item* {
	qui replace `v'="1" if `v'=="+"
	qui replace `v'="0" if `v'=="A"|`v'=="B"|`v'=="C"|`v'=="D"
	qui replace `v'="" if `v'=="*"
	qui destring `v', replace
	}
sort sasid year
save "`saves'\mcas2003items.dta", replace 
drop *item*
sort sasid year
save "`saves'\mcas2003y.dta", replace
clear
use "`raw'\mcas2004.dta"
gen year=2004
foreach v of varlist *item* {
	qui replace `v'="1" if `v'=="+"
	qui replace `v'="0" if `v'=="A"|`v'=="B"|`v'=="C"|`v'=="D"
	qui replace `v'="" if `v'=="*"
	qui destring `v', replace
	}
sort sasid year
save "`saves'\mcas2004items.dta", replace 
drop *item*
sort sasid year
cap tostring lepfirst, replace
save "`saves'\mcas2004y.dta", replace
clear
use "`raw'\mcas2005.dta"
gen year=2005
foreach v of varlist *item* {
	qui replace `v'="1" if `v'=="+"
	qui replace `v'="0" if `v'=="A"|`v'=="B"|`v'=="C"|`v'=="D"
	qui replace `v'="" if `v'=="*"
	qui destring `v', replace
	}
sort sasid year
save "`saves'\mcas2005items.dta", replace 
drop *item* 
sort sasid year
cap tostring lepfirst, replace
save "`saves'\mcas2005y.dta", replace
clear
use "`raw'\mcas2006.dta"
gen year=2006
foreach v of varlist *item* {
	qui replace `v'="1" if `v'=="+"
	qui replace `v'="0" if `v'=="A"|`v'=="B"|`v'=="C"|`v'=="D"
	qui replace `v'="" if `v'=="*"
	qui destring `v', replace
	}
sort sasid year
save "`saves'\mcas2006items.dta", replace 
drop *item* 
sort sasid year
cap tostring lepfirst, replace
save "`saves'\mcas2006y.dta", replace

use "`raw'\mcas2007.dta"
gen year=2007
foreach v of varlist *item* {
	qui replace `v'="1" if `v'=="+"
	qui replace `v'="0" if `v'=="A"|`v'=="B"|`v'=="C"|`v'=="D"
	qui replace `v'="" if `v'=="*"
	qui destring `v', replace
	}
sort sasid year
save "`saves'\mcas2007items.dta", replace 
drop *item* 
rename race_off race7
rename race5 race
sort sasid year
cap tostring lepfirst, replace
save "`saves'\mcas2007y.dta", replace

use "`raw'\mcas2008.dta"
gen year=2008
foreach v of varlist *item* {
	qui replace `v'="1" if `v'=="+"
	qui replace `v'="0" if `v'=="A"|`v'=="B"|`v'=="C"|`v'=="D"
	qui replace `v'="" if `v'=="*"
	qui destring `v', replace
	}
sort sasid year
save "`saves'\mcas2008items.dta", replace
drop *item* q*
rename race_off race7
*rename race05 race
sort sasid year
cap tostring lepfirst, replace
save "`saves'\mcas2008y.dta", replace

use "`raw'\mcas2009.dta"
gen year=2009
rename *, lower
foreach v of varlist *item* {
	qui replace `v'="1" if `v'=="+"
	qui replace `v'="0" if `v'=="A"|`v'=="B"|`v'=="C"|`v'=="D"
	qui replace `v'="" if `v'=="*"
	qui destring `v', replace
	}
*many items are string
foreach v of varlist     migrant_off-plan504  lepfirst  {
	cap qui destring `v', replace
	}
sort sasid year
save "`saves'\mcas2009items.dta", replace
drop *item* 
rename race_off race7
*rename race05 race
sort sasid year
cap tostring lepfirst, replace
save "`saves'\mcas2009y.dta", replace

use "`raw'\mcas2010.dta"
rename *, lower
gen year=2010
foreach v of varlist *item* {
	qui replace `v'="1" if `v'=="+"
	qui replace `v'="0" if `v'=="A"|`v'=="B"|`v'=="C"|`v'=="D"
	qui replace `v'="" if `v'=="*"
	qui destring `v', replace
	}
*many items are string
foreach v of varlist     migrant_off-plan504  lepfirst  {
	cap qui destring `v', replace
	}
sort sasid year
save "`saves'\mcas2010items.dta", replace
drop *item*
rename race_off race7
*rename race05 race
sort sasid year
cap tostring lepfirst, replace
save "`saves'\mcas2010y.dta", replace

use "`raw'\mcas2011.dta"
rename *, lower
gen year=2011
foreach v of varlist *item* {
	qui replace `v'="1" if `v'=="+"
	qui replace `v'="0" if `v'=="A"|`v'=="B"|`v'=="C"|`v'=="D"
	qui replace `v'="" if `v'=="*"
	qui destring `v', replace
	}
*many items are string
foreach v of varlist     migrant_off-plan504  lepfirst  {
	cap qui destring `v', replace
	}
sort sasid year
save "`saves'\mcas2011items.dta", replace
drop *item*
rename race_off race7
*rename race05 race
sort sasid year
cap tostring lepfirst, replace
save "`saves'\mcas2011y.dta", replace

use "`raw'\mcas2012.dta"
rename *, lower
gen year=2012
foreach v of varlist *item* {
	qui replace `v'="1" if `v'=="+"
	qui replace `v'="0" if `v'=="A"|`v'=="B"|`v'=="C"|`v'=="D"
	qui replace `v'="" if `v'=="*"
	qui destring `v', replace
	}
*many items are string
foreach v of varlist      freelunch_off-plan504  {
	cap qui destring `v', replace
	}
sort sasid year
save "`saves'\mcas2012items.dta", replace
drop *item*
rename race_off race7
*rename race05 race
sort sasid year
save "`saves'\mcas2012y.dta", replace


use "`raw'\mcas2013.dta"
rename *, lower
gen year=2013
foreach v of varlist *item* {
	qui replace `v'="1" if `v'=="+"
	qui replace `v'="0" if `v'=="A"|`v'=="B"|`v'=="C"|`v'=="D"
	qui replace `v'="" if `v'=="*"
	qui destring `v', replace
	}
*many items are string
foreach v of varlist      freelunch_off-plan504  {
	cap qui destring `v', replace
	}
sort sasid year
save "`saves'\mcas2013items.dta", replace
drop *item*
rename race_off race7
*rename race05 race
sort sasid year
save "`saves'\mcas2013y.dta", replace

use "`raw'\mcas2014.dta"
rename *, lower
gen year=2014
foreach v of varlist *item* {
	qui replace `v'="1" if `v'=="+"
	qui replace `v'="0" if `v'=="A"|`v'=="B"|`v'=="C"|`v'=="D"
	qui replace `v'="" if `v'=="*"
	qui destring `v', replace
	}
*many items are string
foreach v of varlist      freelunc-plan504  {
	cap qui destring `v', replace
	}
sort sasid year
save "`saves'\mcas2014items.dta", replace
drop *item*
rename race_off race7
*rename race05 race
sort sasid year
save "`saves'\mcas2014y.dta", replace

use "`raw'\mcas2015.dta"
rename *, lower
gen year=2015
foreach v of varlist *item* {
	qui replace `v'="1" if `v'=="+"
	qui replace `v'="0" if `v'=="A"|`v'=="B"|`v'=="C"|`v'=="D"
	qui replace `v'="" if `v'=="*"
	qui destring `v', replace
	}
*many items are string
foreach v of varlist      freelunc-plan504  {
	cap qui destring `v', replace
	}
sort sasid year
save "`saves'\mcas2015items.dta", replace
drop *item*
rename race_off race7
*rename race05 race
sort sasid year
save "`saves'\mcas2015y.dta", replace


use "`raw'\mcas2016.dta"
rename *, lower
gen year=2016

foreach v of varlist *item* {
	cap qui replace `v'="1" if `v'=="+"
	cap qui replace `v'="0" if `v'=="A"|`v'=="B"|`v'=="C"|`v'=="D"
	cap qui replace `v'="" if `v'=="*"
	cap qui destring `v', replace
	}
*many items are string
*foreach v of varlist      freelunc-plan504  {
*	cap qui destring `v', replace
*	}
sort sasid year
save "`saves'\mcas2016items.dta", replace
drop *item*
rename race_off race7
*rename race05 race
sort sasid year
save "`saves'\mcas2016y.dta", replace

use "`raw'\mcas2017.dta"
rename *, lower
gen year=2017
foreach v of varlist *item* {
	qui replace `v'="1" if `v'=="+"
	qui replace `v'="0" if `v'=="A"|`v'=="B"|`v'=="C"|`v'=="D"
	qui replace `v'="" if `v'=="*"
	qui destring `v', replace
	}
*many items are string
*foreach v of varlist      freelunc-plan504  {
*	cap qui destring `v', replace
*	}
sort sasid year
save "`saves'\mcas2017items.dta", replace
drop *item*
*rename race_off race7
*rename race05 race
sort sasid year
save "`saves'\mcas2017y.dta", replace

use "`raw'\mcas2018.dta"
rename *, lower
gen year=2018
foreach v of varlist *item* {
	qui replace `v'="1" if `v'=="+"
	qui replace `v'="0" if `v'=="A"|`v'=="B"|`v'=="C"|`v'=="D"
	qui replace `v'="" if `v'=="*"
	qui destring `v', replace
	}
*many items are string
*foreach v of varlist      freelunc-plan504  {
*	cap qui destring `v', replace
*	}
sort sasid year
save "`saves'\mcas2018items.dta", replace
drop *item*
*rename race_off race7
*rename race05 race
sort sasid year
save "`saves'\mcas2018y.dta", replace

use "`raw'\mcas2019.dta"
rename *, lower
gen year=2019
foreach v of varlist *item* {
	qui replace `v'="1" if `v'=="+"
	qui replace `v'="0" if `v'=="A"|`v'=="B"|`v'=="C"|`v'=="D"
	qui replace `v'="" if `v'=="*"
	qui destring `v', replace
	}
*many items are string
*foreach v of varlist      freelunc-plan504  {
*	cap qui destring `v', replace
*	}
sort sasid year
save "`saves'\mcas2019items.dta", replace
drop *item*
*rename race_off race7
*rename race05 race
sort sasid year
save "`saves'\mcas2019y.dta", replace

use "`raw'\mcas2020.dta"
rename *, lower
gen year=2020
foreach v of varlist *item* {
	qui replace `v'="1" if `v'=="+"
	qui replace `v'="0" if `v'=="A"|`v'=="B"|`v'=="C"|`v'=="D"
	qui replace `v'="" if `v'=="*"
	qui destring `v', replace
	}
*many items are string
*foreach v of varlist      freelunc-plan504  {
*	cap qui destring `v', replace
*	}
sort sasid year
save "`saves'\mcas2020items.dta", replace
drop *item*
*rename race_off race7
*rename race05 race
sort sasid year
save "`saves'\mcas2020y.dta", replace

use "`raw'\mcas2021.dta"
rename *, lower
gen year=2021
foreach v of varlist *item* {
	qui replace `v'="1" if `v'=="+"
	qui replace `v'="0" if `v'=="A"|`v'=="B"|`v'=="C"|`v'=="D"
	qui replace `v'="" if `v'=="*"
	qui destring `v', replace
	}
*many items are string
*foreach v of varlist      freelunc-plan504  {
*	cap qui destring `v', replace
*	}
sort sasid year
save "`saves'\mcas2021items.dta", replace
drop *item*
*rename race_off race7
*rename race05 race
sort sasid year
save "`saves'\mcas2021y.dta", replace

use "`raw'\mcas2022.dta"
rename *, lower
gen year=2022
foreach v of varlist *item* {
	qui replace `v'="1" if `v'=="+"
	qui replace `v'="0" if `v'=="A"|`v'=="B"|`v'=="C"|`v'=="D"
	qui replace `v'="" if `v'=="*"
	qui destring `v', replace
	}
*many items are string
*foreach v of varlist      freelunc-plan504  {
*	cap qui destring `v', replace
*	}
sort sasid year
save "`saves'\mcas2022items.dta", replace
drop *item*
*rename race_off race7
*rename race05 race
sort sasid year
save "`saves'\mcas2022y.dta", replace

use "`raw'\mcas2023.dta"
rename *, lower
gen year=2023
foreach v of varlist *item* {
	qui replace `v'="1" if `v'=="+"
	qui replace `v'="0" if `v'=="A"|`v'=="B"|`v'=="C"|`v'=="D"
	qui replace `v'="" if `v'=="*"
	qui destring `v', replace
	}
*many items are string
*foreach v of varlist      freelunc-plan504  {
*	cap qui destring `v', replace
*	}
sort sasid year
save "`saves'\mcas2023items.dta", replace
drop *item*
*rename race_off race7
*rename race05 race
sort sasid year
save "`saves'\mcas2023y.dta", replace

}

if "${append}"=="1"{
	
use "`saves'\mcas2023y.dta", clear
compress
cap drop srbstudidmarch 
cap drop lblstudidmarch 
cap drop srbstudidmay 
cap drop lblstudidmay 
cap drop bookletnumber 
cap drop bookletnumbermay 
cap drop bookletnumbermarch  
cap drop district 
cap drop adminyear 
cap drop mcas_rowid
cap drop enumin
cap drop eassess
cap drop amendela
cap drop mnumin
cap drop massess
cap drop amendmat
cap drop snumin
cap drop sassess
cap drop amendsci
cap drop datachanged
cap drop lepfirst
	
use "`saves'\mcas2022y.dta", clear
compress
cap drop srbstudidmarch 
cap drop lblstudidmarch 
cap drop srbstudidmay 
cap drop lblstudidmay 
cap drop bookletnumber 
cap drop bookletnumbermay 
cap drop bookletnumbermarch  
cap drop district 
cap drop adminyear 
cap drop mcas_rowid
cap drop enumin
cap drop eassess
cap drop amendela
cap drop mnumin
cap drop massess
cap drop amendmat
cap drop snumin
cap drop sassess
cap drop amendsci
cap drop datachanged
cap drop lepfirst
	
use "`saves'\mcas2021y.dta", clear
compress
cap drop srbstudidmarch 
cap drop lblstudidmarch 
cap drop srbstudidmay 
cap drop lblstudidmay 
cap drop bookletnumber 
cap drop bookletnumbermay 
cap drop bookletnumbermarch  
cap drop district 
cap drop adminyear 
cap drop mcas_rowid
cap drop enumin
cap drop eassess
cap drop amendela
cap drop mnumin
cap drop massess
cap drop amendmat
cap drop snumin
cap drop sassess
cap drop amendsci
cap drop datachanged
cap drop lepfirst


use "`saves'\mcas2020y.dta", clear
compress
cap drop srbstudidmarch 
cap drop lblstudidmarch 
cap drop srbstudidmay 
cap drop lblstudidmay 
cap drop bookletnumber 
cap drop bookletnumbermay 
cap drop bookletnumbermarch  
cap drop district 
cap drop adminyear 
cap drop mcas_rowid
cap drop enumin
cap drop eassess
cap drop amendela
cap drop mnumin
cap drop massess
cap drop amendmat
cap drop snumin
cap drop sassess
cap drop amendsci
cap drop datachanged
cap drop lepfirst

	
use "`saves'\mcas2019y.dta", clear
compress
cap drop srbstudidmarch 
cap drop lblstudidmarch 
cap drop srbstudidmay 
cap drop lblstudidmay 
cap drop bookletnumber 
cap drop bookletnumbermay 
cap drop bookletnumbermarch  
cap drop district 
cap drop adminyear 
cap drop mcas_rowid
cap drop enumin
cap drop eassess
cap drop amendela
cap drop mnumin
cap drop massess
cap drop amendmat
cap drop snumin
cap drop sassess
cap drop amendsci
cap drop datachanged
cap drop lepfirst

	
use "`saves'\mcas2018y.dta", clear
compress
cap drop srbstudidmarch 
cap drop lblstudidmarch 
cap drop srbstudidmay 
cap drop lblstudidmay 
cap drop bookletnumber 
cap drop bookletnumbermay 
cap drop bookletnumbermarch  
cap drop district 
cap drop adminyear 
cap drop mcas_rowid
cap drop enumin
cap drop eassess
cap drop amendela
cap drop mnumin
cap drop massess
cap drop amendmat
cap drop snumin
cap drop sassess
cap drop amendsci
cap drop datachanged
cap drop lepfirst

	
use "`saves'\mcas2017y.dta", clear
compress
cap drop srbstudidmarch 
cap drop lblstudidmarch 
cap drop srbstudidmay 
cap drop lblstudidmay 
cap drop bookletnumber 
cap drop bookletnumbermay 
cap drop bookletnumbermarch  
cap drop district 
cap drop adminyear 
cap drop mcas_rowid
cap drop enumin
cap drop eassess
cap drop amendela
cap drop mnumin
cap drop massess
cap drop amendmat
cap drop snumin
cap drop sassess
cap drop amendsci
cap drop datachanged
cap drop lepfirst


use "`saves'\mcas2016y.dta", clear
compress
cap drop srbstudidmarch 
cap drop lblstudidmarch 
cap drop srbstudidmay 
cap drop lblstudidmay 
cap drop bookletnumber 
cap drop bookletnumbermay 
cap drop bookletnumbermarch  
cap drop district 
cap drop adminyear 
cap drop mcas_rowid
cap drop enumin
cap drop eassess
cap drop amendela
cap drop mnumin
cap drop massess
cap drop amendmat
cap drop snumin
cap drop sassess
cap drop amendsci
cap drop datachanged
cap drop lepfirst
	
	
use "`saves'\mcas2015y.dta", clear
compress
cap drop srbstudidmarch 
cap drop lblstudidmarch 
cap drop srbstudidmay 
cap drop lblstudidmay 
cap drop bookletnumber 
cap drop bookletnumbermay 
cap drop bookletnumbermarch  
cap drop district 
cap drop adminyear 
cap drop mcas_rowid
cap drop enumin
cap drop eassess
cap drop amendela
cap drop mnumin
cap drop massess
cap drop amendmat
cap drop snumin
cap drop sassess
cap drop amendsci
cap drop datachanged
cap drop lepfirst

append using  "`saves'\mcas2014y.dta"
compress
cap drop srbstudidmarch 
cap drop lblstudidmarch 
cap drop srbstudidmay 
cap drop lblstudidmay 
cap drop bookletnumber 
cap drop bookletnumbermay 
cap drop bookletnumbermarch  
cap drop district 
cap drop adminyear 
cap drop mcas_rowid
cap drop enumin
cap drop eassess
cap drop amendela
cap drop mnumin
cap drop massess
cap drop amendmat
cap drop snumin
cap drop sassess
cap drop amendsci
cap drop datachanged
cap drop lepfirst

append using "`saves'\mcas2013y.dta"
compress
cap drop srbstudidmarch 
cap drop lblstudidmarch 
cap drop srbstudidmay 
cap drop lblstudidmay 
cap drop bookletnumber 
cap drop bookletnumbermay 
cap drop bookletnumbermarch  
cap drop district 
cap drop adminyear 
cap drop mcas_rowid
cap drop enumin
cap drop eassess
cap drop amendela
cap drop mnumin
cap drop massess
cap drop amendmat
cap drop snumin
cap drop sassess
cap drop amendsci
cap drop datachanged
append using "`saves'\mcas2012y.dta"
compress
cap drop srbstudidmarch 
cap drop lblstudidmarch 
cap drop srbstudidmay 
cap drop lblstudidmay 
cap drop bookletnumber 
cap drop bookletnumbermay 
cap drop bookletnumbermarch  
cap drop district 
cap drop adminyear 
cap drop mcas_rowid
cap drop enumin
cap drop eassess
cap drop amendela
cap drop mnumin
cap drop massess
cap drop amendmat
cap drop snumin
cap drop sassess
cap drop amendsci
cap drop datachanged
append using "`saves'\mcas2011y.dta"
compress
cap drop srbstudidmarch 
cap drop lblstudidmarch 
cap drop srbstudidmay 
cap drop lblstudidmay 
cap drop bookletnumber 
cap drop bookletnumbermay 
cap drop bookletnumbermarch  
cap drop district 
cap drop adminyear 
cap drop mcas_rowid
cap drop enumin
cap drop eassess
cap drop amendela
cap drop mnumin
cap drop massess
cap drop amendmat
cap drop snumin
cap drop sassess
cap drop amendsci
cap drop datachanged
append using "`saves'\mcas2010y.dta"
compress
cap drop srbstudidmarch 
cap drop lblstudidmarch 
cap drop srbstudidmay 
cap drop lblstudidmay 
cap drop bookletnumber 
cap drop bookletnumbermay 
cap drop bookletnumbermarch  
cap drop district 
cap drop adminyear 
cap drop mcas_rowid
cap tostring scitry, replace 
append using "`saves'\mcas2008y.dta"
compress
cap drop srbstudidmarch 
cap drop lblstudidmarch 
cap drop srbstudidmay 
cap drop lblstudidmay 
cap drop bookletnumber 
cap drop bookletnumbermay 
cap drop bookletnumbermarch  
cap drop district 
cap drop adminyear 
cap drop mcas_rowid
append using "`saves'\mcas2009y.dta"
compress
cap destring scitry, replace
cap drop srbstudidmarch 
cap drop lblstudidmarch 
cap drop srbstudidmay 
cap drop lblstudidmay 
cap drop bookletnumber 
cap drop bookletnumbermay 
cap drop bookletnumbermarch  
cap drop district 
cap drop adminyear 
cap drop mcas_rowid
append using "`saves'\mcas2007y.dta"
compress
cap drop srbstudidmarch 
cap drop lblstudidmarch 
cap drop srbstudidmay 
cap drop lblstudidmay 
cap drop bookletnumber 
cap drop bookletnumbermay 
cap drop bookletnumbermarch  
cap drop district 
cap drop adminyear 
cap drop mcas_rowid
append using "`saves'\mcas2006y.dta"
compress
cap drop srbstudidmarch 
cap drop lblstudidmarch 
cap drop srbstudidmay 
cap drop lblstudidmay 
cap drop bookletnumber 
cap drop bookletnumbermay 
cap drop bookletnumbermarch  
cap drop district 
cap drop adminyear 
cap drop mcas_rowid
append using "`saves'\mcas2005y.dta"
compress
cap drop srbstudidmarch 
cap drop lblstudidmarch 
cap drop srbstudidmay 
cap drop lblstudidmay 
cap drop bookletnumber 
cap drop bookletnumbermay 
cap drop bookletnumbermarch  
cap drop district 
cap drop adminyear 
cap drop mcas_rowid
append using "`saves'\mcas2004y.dta"
compress
cap drop srbstudidmarch 
cap drop lblstudidmarch 
cap drop srbstudidmay 
cap drop lblstudidmay 
cap drop bookletnumber 
cap drop bookletnumbermay 
cap drop bookletnumbermarch  
cap drop district 
cap drop adminyear 
cap drop mcas_rowid
append using "`saves'\mcas2003y.dta"
compress
cap drop srbstudidmarch 
cap drop lblstudidmarch 
cap drop srbstudidmay 
cap drop lblstudidmay 
cap drop bookletnumber 
cap drop bookletnumbermay 
cap drop bookletnumbermarch  
cap drop district 
cap drop adminyear 
cap drop mcas_rowid
append using "`saves'\mcas2002y.dta"
compress
cap drop srbstudidmarch 
cap drop lblstudidmarch 
cap drop srbstudidmay 
cap drop lblstudidmay 
cap drop bookletnumber 
cap drop bookletnumbermay 
cap drop bookletnumbermarch  
cap drop district 
cap drop adminyear 
cap drop mcas_rowid

sort sasid year

save "`saves'\MCASallyears.dta", replace

*this is named differently in 2014!!!! SRC
*cap replace wpcompconv=wpcompco if wpcompconv==""  &year==2014
*cap drop wpcompco

foreach v of varlist wptopdev wpcom* {
	replace `v'="0" if `v'=="BL"|`v'=="NS"
	replace `v'="0" if `v'=="IE"|`v'=="LO"|`v'=="OT"|`v'=="PS"|`v'=="VI"
	}
	
foreach v of varlist prepase prepasm movein moveout *form {
	replace `v' ="1" if `v'=="Y"
	replace `v'="0" if `v'=="N"
	}

foreach v of varlist sprp_dis sprp_sch system school grade yrsinmass natureofdis levelofneed sending sendsch  ///
	octenr  sprp_sch_baseline wptopdev wpcom* prepase prepasm movein moveout *form {
	replace `v'=trim(itrim(`v'))
	destring(`v'),  ignore(" " "+"  "*") replace
	}
	
destring sasid, replace force

save "`saves'\MCASdestrung.dta", replace
}

if "${clean1}"=="1"{
use "`saves'\MCASdestrung.dta", clear
drop lastname firstname mi

*destring DOB
replace dob=subinstr(dob," ","",.)
replace dob=subinstr(dob,"//","",.)
replace dob=subinstr(dob,"*","",.)
gen length=length(dob)
gen dob1=date(dob, "MDY", 2010) if length==8 |length==10
*figure out a way to deal with dob's that make sense but aren't in correct format
*drop dob 
replace dob=subinstr(dob,"/","",.)
destring dob, replace
rename dob doborig
rename dob1 dob
format dob %td

/*TEST VARIABLES*/

*ela

replace ealt="1" if ealt=="Y"
destring ealt, replace
label var ealt "Took alternative ELA assessment"
replace malt="1" if malt=="Y"
destring malt, replace
label var malt "Took alternative Math assessment"
replace salt="1" if salt=="Y"
destring salt, replace
label var salt "Took alternative Science assessment"
label var ecomplexity "ELA complexity level in Alt test"
label var mcomplexity "Math complexity level in Alt test"
label var scomplexity "Science complexity level in Alt test"

label def teststat 1 "Tested" 0 "Not Tested" 2 "Alternative Exam"
foreach v of varlist eteststat mteststat steststat {
	replace `v'="1" if `v'=="T" | `v'=="TR"
	replace `v'="0" if `v'=="NTA"|`v'=="NTM"|`v'=="NTO"|`v'=="NTL"
	replace `v'="2" if `v'=="ALT"
	destring `v', replace
	label values `v' teststat
	}
label var eteststat "Testing Status ELA"
label var mteststat "Testing Status Math"
label var steststat "Testing Status Science"

label var wptopdev "writing topic development score"
label var wpcompconv "writing composition convention score"

drop *form

label var erawsc "Raw score ELA"
label var escaleds "Scaled score ELA"
label var eperflev "Performance level ELA"
label var mrawsc "Raw score Math"
label var mscaleds "Scaled score Math"
label var mperflev "Performance level Math"
label var srawsc "Raw score Science"
label var sscaleds "Scaled score Science"
label var sperflev "Performance level Science"
rename  sprp_sch masscode

/*deal with duplicates */
replace sasid=. if sasid==411|sasid==0|sasid==1000000000|sasid==1|sasid==0000000411
duplicates tag sasid year, generate(dup)
gen DUPLICATE=1 if dup>=1
drop dup
label var DUPLICATE "Duplicate sasid and year"
tab DUP, mis


local subj e m s
local levs A_A P_A PRO NIA AWR EMG ING
foreach l of local subj{
	foreach w of local levs{
		replace `l'alt=1 if `l'perflev=="`w'"
		}
}

duplicates drop
save "`saves'\mcasallvars.dta", replace
}
      
 if "${clean2}"=="1"{
 *everything else comes from SIMS, so can drop most stuff here CW 3/24/2011
use sasid year  grade  erawsc escaleds mrawsc mscaleds ///
	srawsc sscaleds wp*v wp* *perflev  *alt *complexity *teststat masscode  dob sprp_dis scitry school using ///
	"`saves'\mcasallvars.dta", clear

*drop kids with no sasid, can't merge them
drop if sasid==.  
compress

/*make standardized test score variables*/
* Note, writing exams only have observations for grades 4,7,&10

* combine writing exams:
	assert wptopdev==. if wpcompconv==.

	
	gen writ = wptopdev + wpcompconv
		replace writ = . if wpcompconv!=. & wptopdev==.
		
	*EXCLUDE 2020

foreach s of varlist erawsc mrawsc srawsc wptopdev wpcom* writ {
	qui destring `s', replace force
	qui g c_state_`s'= `s'
	foreach y of numlist 2002/2019 2021/2022 {
		foreach g of numlist 3/8 10{
			qui summ `s' if `y'==year &`g'==grade
			qui replace c_state_`s' = (`s'-r(mean))/r(sd) if `y'==year &`g'==grade
			}
		}
	}
	
* Science in HS needs to be centered based on subject scitry and not grade
replace c_state_srawsc = . if grade==10
	foreach y of numlist  2008/2019 2021/2022 {
	forvalues subj = 1/4 {
		qui summ srawsc if `y'==year & `subj'==scitry
		qui replace c_state_srawsc = (srawsc-r(mean))/r(sd) if `y'==year &`subj'==scitry
	}
}	

* For now, don't use results in 2015/2016 except for 10th grade (did a combo of MCAS and PARCC)
foreach s of varlist erawsc mrawsc srawsc wptopdev wpcom* writ {
		foreach y of numlist 2015/2016 {
		foreach g of numlist 3/8 {
			replace c_state_`s' = . if `y'==year &`g'==grade
			}
		}
		foreach y of numlist 2020 { //exclude due to pandemic
		foreach g of numlist 3/8 10 {
			replace c_state_`s' = . if `y'==year &`g'==grade
			}
		}
	}

compress
summ c_*

compress
summ c_*
	
drop if erawsc==.&mrawsc==.&srawsc==.&wptopdev==.
duplicates drop  year sasid  masscode grade erawsc escaleds mrawsc mscaleds srawsc sscaleds wptopdev wpcom* dob eperflev mperflev, force
	
qui duplicates tag sasid year, generate(dup)
qui replace dup=1 if dup>=1
tab dup

qui gen testrepeat10=1 if dup>=1&grade==10
qui label var testrepeat10 "Duplicate, 10th grader with mult tests, same year"
	
qui sort sasid year
save "`saves'\mcaskeyvars.dta", replace
}
if $clean3==1{
use sasid year grade c_* *scaled* using "$raw\saves\mcaskeyvars.dta", replace
format sasid %12.0f
*keep only first mcas per grade
bys sasid grade:  egen minyear=min(year)
keep if year==minyear
drop minyear

*remaining dups are people who have two scores in same grade and year. 
* impossible to tell which is first, so sample
sample 1, count by(sasid year grade)

duplicates report sasid year grade
save "$data_clean\mcas_long.dta", replace

ren year mcasyear
reshape wide c_* *scaleds mcasyear, i(sasid) j(grade)

duplicates report sasid
save "$data_clean\mcas_wide.dta", replace


}
